package com.bdqn.dao.Impl;

import com.bdqn.dao.PageQueryDao;
import com.bdqn.entity.CheckWork;
import com.bdqn.entity.Employee;
import com.bdqn.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class PageQueryDaoImpl implements PageQueryDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public Employee FindById(String empId) {
        String sql = "select * from employee where empId=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Employee>(Employee.class),empId);
    }

    @Override
    public List<CheckWork> findByPage(String empId, int depId, int offset, int pageSize, String end, String start) {
        String sql = "select depname,e.empId,name,date,starttime,endtime,latecome,absenteeism,leave_early from employee e,department d,checkwork c " +
                "where e.empId=c.empId and e.depId=d.depId and  e.empId=? and d.depId=? and date between ? and ? limit ?,? ";

        return template.query(sql,new BeanPropertyRowMapper<CheckWork>(CheckWork.class),empId,depId,start,end,offset,pageSize);
    }

    @Override
    public List<CheckWork> findByPage(String empId, int depId, int offset, int pageSize) {
        String sql = "select depname,e.empId,name,date,starttime,endtime,latecome,absenteeism,leave_early " +
                "from employee e,department d,checkwork c where e.empId=c.empId and e.depId=d.depId and e.empId=? and d.depId=? limit ?,?";

        return template.query(sql,new BeanPropertyRowMapper<CheckWork>(CheckWork.class),empId,depId,offset,pageSize);
    }

    @Override
    public int findById(String empId, String end, String start) {
        StringBuilder sql = new StringBuilder("select count(*) from checkwork where empId=? and 1=1 ");
        List<Object> params = new ArrayList<>();
        params.add(empId);
        if(end.equals(start)){
            sql.append(" and date=? ");
            params.add(start);
        }else{
            sql.append(" and date between ? and ?");
            params.add(start);
            params.add(end);
        }
        return template.queryForObject(sql.toString(),Integer.class,params.toArray());

    }

    @Override
    public int findById(String empId) {
        String sql = "select count(*) from checkwork where empId=?";
        return template.queryForObject(sql,Integer.class,empId);
    }
}
